package edu.nctu.csie.jichang.database.save;

import java.io.BufferedOutputStream;
import java.io.File;
import java.io.FileOutputStream;
import java.io.OutputStreamWriter;
import java.io.Writer;
import java.util.List;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import edu.nctu.csie.jichang.database.dbinfo.LoginInfo;
import edu.nctu.csie.jichang.database.model.cell.AbstractRootElement;
import edu.nctu.csie.jichang.database.model.cell.ColumnInfo;
import edu.nctu.csie.jichang.database.model.cell.DBDatabase;
import edu.nctu.csie.jichang.database.model.cell.DBRow;
import edu.nctu.csie.jichang.database.model.cell.DBSchema;
import edu.nctu.csie.jichang.database.model.cell.DBTable;
import edu.nctu.csie.jichang.database.model.cell.DBTableHeader;
import edu.nctu.csie.jichang.database.util.DateUtil;
import edu.nctu.csie.jichang.database.util.StringUtil;


/**
 * SaveSQLDB
 * @author Jichang
 */
public class SaveSQLFile implements ISave {
	
	private static final Logger LOG = LoggerFactory.getLogger(SaveSQLFile.class);
	
	private static final String NEWLINE = StringUtil.CHANGE_LINE;
	
	/**
	 * (non-Javadoc)
	 * @see edu.nctu.csie.jichang.database.save.ISave#doSave(java.io.File, com.jichang.dbtools.model.cell.DBDatabase)
	 */
	public void doSave(File pFile, DBDatabase pDatabase) throws Exception {
		
		if(!pFile.getParentFile().exists())pFile.getParentFile().mkdirs();
		
		LOG.info("Saveing   - File "+ pFile.getName());
		BufferedOutputStream tBufferOut = new BufferedOutputStream(new FileOutputStream(pFile));
		Writer tWr = new OutputStreamWriter(tBufferOut,"UTF-8");
		tWr.write("/****************************************/"+NEWLINE);
		tWr.write("/******* Database = "+pDatabase.getDatabaseName()+" at "+ DateUtil.getFullDate()+"  ************/"+NEWLINE);
		tWr.write("/****************************************/"+NEWLINE);
		tWr.write(NEWLINE);
		String tSchema[] = pDatabase.getSchemaNames();
		for(int i = tSchema.length-1;i>=0;i--){
			saveISchema(pDatabase.getSchema(tSchema[i]),tWr);
			tWr.flush();
		}
		tWr.flush();
		tWr.close();
		tBufferOut.close();
		LOG.info("Finishing - File "+ pFile.getName());
	}
	

	public void saveISchema(DBSchema pMySchema,Writer pWr) throws Exception {
		pWr.write(NEWLINE);
		pWr.write("/****************************************/"+NEWLINE);
		pWr.write("/******* Schema = "+pMySchema.getSchemaName()+" ************/"+NEWLINE);
		pWr.write("/****************************************/"+NEWLINE);
		pWr.write("USE "+pMySchema.getSchemaName() +NEWLINE);
		pWr.write(NEWLINE);
		pWr.write("/*"+NEWLINE);
		for(DBTable d: pMySchema.getAllTables()){
			pWr.write(" DELETE FROM " + d.getTableName() +";"+NEWLINE);
		}		
		pWr.write("*/"+NEWLINE);
		for(DBTable d: pMySchema.getAllTables()){
			saveITable(d,pWr);
		}
		pWr.write(NEWLINE);
	}
	
	public void saveITable(DBTable pTable, Writer pWr) throws Exception {
		pWr.write(NEWLINE);
		pWr.write("/****************************************/"+NEWLINE);
		pWr.write("/******* Table = " + pTable.getTableName()+" ************/"+NEWLINE);
		pWr.write("/****************************************/"+NEWLINE);
		
		pWr.write("SET IDENTITY_INSERT "+pTable.getTableName()+" ON"+NEWLINE);
		for (DBRow d:pTable.getAllRows()) {
			pWr.write(saveRow(pTable.getTableName(),pTable.getTableHeader(),d)+NEWLINE);
		}
		pWr.write("SET IDENTITY_INSERT "+pTable.getTableName()+" OFF"+NEWLINE);
	}
	
	public StringBuffer saveRow(String pTableName, DBTableHeader pTableHeader,DBRow pIRow) throws Exception {
		StringBuffer tSQL = new StringBuffer();
		StringBuffer tValue = new StringBuffer();
		tSQL.append("Insert Into ").append(pTableName).append(" ( ");
		for (String s:pTableHeader.getColumnNames()){
			tSQL.append(s).append(",");
			if(pIRow.hasCell(s)){
				tValue.append(getRightType(pTableHeader.getColumnType(s),pIRow.getCell(s).getName())).append(",");
			}else{
				tValue.append("null").append(",");
			}
		}
		tSQL.deleteCharAt(tSQL.length()-1);
		tValue.deleteCharAt(tValue.length()-1);
		tSQL.append(" ) Values ( ").append(tValue).append(" ); ");
		return tSQL;
	}
	

	/**
	 * @param pType type
	 * @param pValue Value
	 * @return Value
	 * @throws Exception if Error
	 */
	private String getRightType(AbstractRootElement<?> pType, String pValue) throws Exception{
		if (pValue!=null && pType instanceof ColumnInfo) {
			ColumnInfo tColumnInfo = (ColumnInfo) pType;
			switch(tColumnInfo.getColumnType()){
				case String:
					return "'"+pValue.replaceAll("'", "''")+"'";
				case Integer:
					return pValue;
				case Date:
	//				return "to_date('"+pValue+"','YYYY/MM/DD HH24:MI:SS')";
					return "'"+pValue+"'";
				case Boolean:
					return pValue;
				case Binary:
					return pValue;
			}
		}
		return null;
	}

	public void doSave(List<LoginInfo> pConns, DBDatabase pDatabase) throws Exception {
	}
}